﻿using System;
using System.Collections.Generic;
using System.Text;
using ZhCun.Framework.Common;
using ZhCun.Framework.Common.Models.TableModel;
using ZhCun.Framework.Common.Helpers;
using ZhCun.Framework.Common.Exceptions;
using ZhCun.Framework.DataAccess.DataRoute;
using System.Reflection;
using System.Collections;

namespace ZhCun.Framework.DataAccess.BuildSQLText
{
    public abstract class BuildSQL
    {
        #region 构造方法

        public BuildSQL()
        {
            _ModelAnaly = ConfigFactory.CreateModelAnaly();
        }

        #endregion
        #region 变量

        ModelAnaly _ModelAnaly;

        #endregion
       
        /// <summary>
        /// 删除指定的忽略字段
        /// </summary>
        /// <param name="fields">要删除的字段源</param>
        /// <param name="removeFields">指定要删除的字段</param>
        /// <returns>返回删除后的字段列表</returns>
        protected internal string[] RemoveFields(string[] sourceFields, params string[] removeFields)
        {
            if (removeFields == null || removeFields.Length == 0) return sourceFields;
            List<string> list = new List<string>();
            bool ignore;
            for (int i = 0; i < sourceFields.Length; i++)
            {
                ignore = false;
                for (int j = 0; j < removeFields.Length; j++)
                {
                    if (removeFields[j].Equals(sourceFields[i], StringComparison.OrdinalIgnoreCase))
                    {
                        ignore = true;
                        break;
                    }
                }
                if (ignore == false)
                {
                    list.Add(sourceFields[i]);
                }
            }
            return list.ToArray();
        }
        /// <summary>
        /// 获取表名
        /// </summary>
        protected internal string GetTableName<T>() where T : ModelBase, new()
        {
            return _ModelAnaly.GetTableName<T>();
        }
        /// <summary>
        /// 获取所有字段
        /// </summary>
        protected internal string[] GetAllFields<T>() where T : ModelBase, new()
        {
            Dictionary<string, ModelAttribute> modelAttr = _ModelAnaly.GetModelAttribute<T>();
            List<string> fieldList = new List<string>();
            foreach (string item in modelAttr.Keys)
            {
                fieldList.Add(modelAttr[item].ColumnName);
            }
            return fieldList.ToArray();
        }
        /// <summary>
        /// 获取Insert语句的字段,会去除自增长列名
        /// </summary>
        protected string[] GetInsertFields<T>() where T : ModelBase, new()
        {
            Dictionary<string, ModelAttribute> modelAttr = _ModelAnaly.GetModelAttribute<T>();
            List<string> fl = new List<string>();
            foreach (string item in modelAttr.Keys)
            {
                if (modelAttr[item].IsIdentity) continue;
                fl.Add(modelAttr[item].ColumnName);
            }
            if (fl == null || fl.Count == 0)
            {
                string errMsg = string.Format("解析{0}类的字段失败", typeof(T));
                throw new FrameworkException(errMsg);
            }
            return fl.ToArray();
        }
        /// <summary>
        /// 获得数据库用的字段格式,[Id],[Name],[Age]
        /// </summary>        
        protected string GetSelectFieldStr(string[] fieldArr)
        {
            if (fieldArr == null || fieldArr.Length == 0) return null;
            StringBuilder fdStr = new StringBuilder();
            for (int i = 0; i < fieldArr.Length - 1; i++)
            {
                //fdStr.AppendFormat("[{0}],", fieldArr[i]);
                //2013.11.10 去掉查询时字段的"[]"(中括号),是因为SQLite冲突,
                //如果需要sql server 中加上[] 那么可在SQLite中重写该方法
                fdStr.AppendFormat("{0},", fieldArr[i]);
            }
            fdStr.AppendFormat("{0}", fieldArr[fieldArr.Length - 1]);
            return fdStr.ToString();
        }
        /// <summary>
        /// 获得主键字段名
        /// </summary>
        protected internal string GetPrimaryKeyName<T>() where T : ModelBase, new()
        {
            Dictionary<string, ModelAttribute> modelAttr = _ModelAnaly.GetModelAttribute<T>();
            foreach (string item in modelAttr.Keys)
            {
                if (modelAttr[item].IsPrimaryKey)
                {
                    return modelAttr[item].ColumnName;
                }
            }
            throw new FrameworkException("获取主键失败");
        }        
        /// <summary>
        /// 反射获取字段名与对应的字段值,Key:数据库字段名(非类的属性名),解决类中属性名与数据字段必须一致的方法
        /// </summary>
        /// <param name="model">实体对象</param>        
        protected Dictionary<string, object> GetModelValue<T>(T model) where T : ModelBase, new()
        {
            Dictionary<string, ModelAttribute> modelAttr = _ModelAnaly.GetModelAttribute<T>();
            Dictionary<string, object> r = new Dictionary<string, object>();
            foreach (string modelAttrName in modelAttr.Keys)
            {
                PropertyInfo proInfo = ReflectionHelper.GetPropertyInfo<T>(modelAttrName);
                string fieldName = modelAttr[modelAttrName].ColumnName;
                object attrValue = proInfo.GetValue(model, null);
                Type type = proInfo.PropertyType;
                //SetDefaultValue(type, ref attrValue);
                r.Add(modelAttr[modelAttrName].ColumnName, attrValue);
            }
            return r;
            ////类的属性名与之属性值
            //var obj = ReflectionHelper.GetPropertyNameAndValue(model);

            //foreach (string item in modelAttr.Keys)
            //{

            //    //TODO:根据Model的属性名设置Model的默认值,在此加入代码
            //    r.Add(modelAttr[item].ColumnName, obj[item]);
            //}
            //return r;
        }        
        /// <summary>
        /// 判断sqlWhere是否包含where,如果不包含则开始位置插入where,
        /// 如果sqlwhere为Null或者为空字符串,则返回false,否则返回true;
        /// </summary>
        protected internal bool CheckSqlWhere(ref string sqlWhere)
        {
            if (string.IsNullOrEmpty(sqlWhere)) return false;
            if (sqlWhere.Trim().Length == 0) return false;
            if (!string.IsNullOrEmpty(sqlWhere))
            {
                if (!sqlWhere.Contains("where"))
                {
                    sqlWhere = sqlWhere.Insert(0, " where ");
                }
            }
            return true;
        }
        /// <summary>
        /// 生成insert语句的SQL文本和参数信息
        /// </summary>
        public virtual void GenerateInsertSQLTextAndParam<T>(out string sqlText, out Dictionary<string, object> dbParams, params T[] models) where T : ModelBase, new()
        {
            StringPlus strSql = new StringPlus();
            //UPDATE:应更改Exception为自定义
            if (models == null || models.Length == 0) throw new Exception("生成增加SQL文本出错,实体为null");
            string[] addFields = GetInsertFields<T>();
            string tableName = GetTableName<T>();
            string addFieldStr = GetSelectFieldStr(addFields);
            strSql.AppendLine("insert into {0}({1})", tableName, addFieldStr);
            Dictionary<string, object> dbParamDict; //db参数字典,用于放每个实体中的值
            dbParams = new Dictionary<string, object>();

            string paramName; //参数名 @Name
            for (int i = 0; i < models.Length; i++)
            {
                dbParamDict = GetModelValue(models[i]);
                strSql.Append("select ");
                for (int j = 0; j < addFields.Length; j++)
                {
                    paramName = string.Format("@{0}_{1}", addFields[j], i);
                    strSql.Append("{0}", paramName);
                    if (j != addFields.Length - 1)
                    {
                        strSql.Append(",");
                    }
                    else
                    {
                        if (i != models.Length - 1)
                        {
                            strSql.AppendLine(" union all ");
                        }
                    }
                    dbParams.Add(paramName, dbParamDict[addFields[j]]);
                }
            }
            sqlText = strSql.ToString();
        }
        /// <summary>
        /// 生成Update语句的sql文本和参数信息
        /// </summary>
        public virtual void GenerateUpdateSQLTextAndParam<T>(out string sqlText, ref Dictionary<string, object> dbParams, string sqlWhere, string[] updateFields, params T[] models) where T : ModelBase, new()
        {
            //UPDATE:应更改Exception为自定义
            if (models == null || models.Length == 0) throw new Exception("生成增加SQL文本出错,实体为null");
            StringPlus strSql = new StringPlus();
            //获得所有字段
            if (updateFields == null || updateFields.Length == 0)
            {
                string[] allFields = GetAllFields<T>();
                updateFields = allFields;
            }
            //主键不更新
            string pkName = GetPrimaryKeyName<T>();
            updateFields = RemoveFields(updateFields, pkName);
            Dictionary<string, object> dbParamDict;
            if (dbParams == null) dbParams = new Dictionary<string, object>();
            string paramName;
            string tableName = GetTableName<T>();
            for (int i = 0; i < models.Length; i++)
            {
                dbParamDict = GetModelValue(models[i]);
                strSql.Append("Update {0} Set ", tableName);
                for (int j = 0; j < updateFields.Length; j++)
                {
                    //if (PrimaryKeyName != updateFields[j])
                    //{
                    paramName = string.Format("@{0}_{1}", updateFields[j], i);
                    strSql.Append("{0}={1}", updateFields[j], paramName);
                    if (j != updateFields.Length - 1)
                    {
                        strSql.Append(",");
                    }
                    dbParams.Add(paramName, dbParamDict[updateFields[j]]);
                    //}
                }
                //按where条件更新
                if (CheckSqlWhere(ref sqlWhere))
                {
                    strSql.AppendLine(sqlWhere);
                }
                else
                {
                    //按主键更新
                    paramName = string.Format("@{0}_{1}", pkName, i); //主键
                    strSql.AppendLine(" where {0}={1} ", pkName, paramName);
                    dbParams.Add(paramName, dbParamDict[pkName]);
                }
                strSql.Append(";"); //必须加上分号否则报错
            }
            sqlText = strSql.ToString();
        }
        /// <summary>
        /// 生成删除的sql文本和参数
        /// </summary>
        public virtual void GenerateDeleteSQLTextAndParam<T>(out string sqlText, out Dictionary<string, object> dbParams, params object[] pkValues) where T : ModelBase, new()
        {
            StringPlus strSql = new StringPlus();
            dbParams = new Dictionary<string, object>();
            string pkParamName;
            string tableName = GetTableName<T>();
            string pkName = GetPrimaryKeyName<T>();
            if (pkValues.Length == 1 && pkValues[0] is Array)
            {
                Array arr = (Array)pkValues[0];
                pkValues = new object[arr.Length];
                ArrayList arrList = new ArrayList();
                foreach (object item in arr)
                {
                    arrList.Add(item);
                }
                pkValues = arrList.ToArray();
            }
            for (int i = 0; i < pkValues.Length; i++)
            {
                //有 \r\n sqlite不认
                pkParamName = string.Format("@{0}_{1}", pkName, i);
                strSql.Append("delete from {0}", tableName);
                strSql.Append(" where {0}={1};", pkName, pkParamName);
                dbParams.Add(pkParamName, pkValues[i]);
            }
            sqlText = strSql.ToString();
        }
        /// <summary>
        /// 判断记录是否存在的sql问和参数
        /// </summary>
        public virtual void GenerateIsExistSQLText<T>(object pkeyValue, string fieldName, object fieldValue, string sqlWhere, ref Dictionary<string, object> dbParams, out string sqlText) where T : ModelBase, new()
        {
            StringPlus strSql = new StringPlus();
            string tableName = GetTableName<T>();
            strSql.AppendLine("Select Count(1) From {0} ", tableName);
            bool isUseWhere = false;
            if (CheckSqlWhere(ref sqlWhere))
            {
                strSql.Append(sqlWhere);
                //strSql.Append(" and ");
                isUseWhere = true;
            }
            else
            {
                strSql.AppendLine(" where ");
                if (dbParams == null) dbParams = new Dictionary<string, object>();
            }
            if (pkeyValue != null)
            {
                if (isUseWhere)
                {
                    strSql.Append(" and ");
                }
                string pkName = GetPrimaryKeyName<T>();
                //不包含该主键值的其它数据是否存在
                string pkParamName = string.Format("@{0}_z", pkName);
                strSql.AppendLine("{0}<>{1} and", pkName, pkParamName);
                dbParams.Add(pkParamName, pkeyValue);
            }
            if (!string.IsNullOrEmpty(fieldName))
            {
                if (isUseWhere)
                {
                    strSql.Append(" and ");
                }
                string fieldParamName = string.Format("@{0}_z", fieldName);
                strSql.AppendLine(" {0}={1}", fieldName, fieldParamName);
                dbParams.Add(fieldParamName, fieldValue);
            }
            sqlText = strSql.ToString();
        }
        /// <summary>
        /// 生成查询的sql文本和参数
        /// </summary>
        public virtual void GenerateSearchSQLTextAndParam<T>(string sqlWhere, Dictionary<string, object> dbParams, string[] orderFields, bool isDesc, out string sqlText, params string[] selectFields) where T : ModelBase, new()
        {
            StringPlus strSql = new StringPlus();

            if (selectFields == null || selectFields.Length == 0)
            {
                selectFields = GetAllFields<T>();
            }
            //string[] selectFieldArr = RemoveFields(fields, _SearchIgnoreFields);
            string searchFieldStr = GetSelectFieldStr(selectFields);
            string tableName = _ModelAnaly.GetTableName<T>();
            strSql.AppendLine("Select {0} from {1} ", searchFieldStr, tableName);
            if (CheckSqlWhere(ref sqlWhere))
            {
                strSql.Append(sqlWhere);
            }
            else
            {
                dbParams = null;
            }
            if (orderFields != null)
            {
                //因为要判断字段是否存在,所以使用这个变量
                List<string> orderFieldList = new List<string>();
                if (orderFields != null)
                {
                    for (int i = 0; i < orderFields.Length; i++)
                    {
                        orderFieldList.Add(orderFields[i]);

                        //不再判断字段是否存在,这样是因为如果不存在报错总比什么都不提示儿出现不排序的好 2013.9.21
                        ////选择字段中,最后一个,或是非第一,或是只有一个字段的情况,加上","是防止所选字段包含字段部分字符的问题.
                        //if (searchFieldStr.ToUpper().Contains(orderFields[i].ToUpper() + ",") ||
                        //    searchFieldStr.ToUpper().Contains("," + orderFields[i].ToUpper()) ||
                        //    searchFieldStr.Trim() == orderFields[i]
                        //    )
                        //{
                        //    orderFieldList.Add(orderFields[i]);
                        //}
                    }
                }
                else
                {
                    //如果没有排序字段,则不排序了 2013.9.21
                    //string pkName = GetPrimaryKeyName<T>();
                    //orderFieldList.Add(pkName);
                }
                string orderFieldStr = GetSelectFieldStr(orderFieldList.ToArray());
                if (!string.IsNullOrEmpty(orderFieldStr))
                {
                    strSql.AppendLine(" Order By {0} ", orderFieldStr);
                    if (isDesc) strSql.Append("desc");
                }
            }
            sqlText = strSql.ToString();
        }
        /// <summary>
        /// 生成统计记录数的sql文本和参数
        /// </summary>
        public virtual void GenerateCountSQLTextAndParam<T>(string sqlWhere, Dictionary<string, object> dbParams, out string sqlText) where T : ModelBase, new()
        {
            StringPlus strSql = new StringPlus();
            string tableName = _ModelAnaly.GetTableName<T>();
            strSql.AppendLine("Select Count(1) as CountRecord From {0} ", tableName);
            if (CheckSqlWhere(ref sqlWhere))
            {
                strSql.Append(sqlWhere);
            }
            else
            {
                dbParams = null;
            }
            sqlText = strSql.ToString();
        }
        /// <summary>
        /// 生成前几行的数据和sql文本和参数
        /// </summary>
        public abstract void GenerateTopCountSQLText<T>(int topCount, string sqlWhere, Dictionary<string, object> dbParams, string[] orderFields, bool isDesc, out string sqlText, params string[] selectFields) where T : ModelBase, new();
        /// <summary>
        /// 生成分页的sql问和参数
        /// </summary>
        public abstract void GeneratePageSQLTextAndParam<T>(int onePageCount, int pageNo, bool isDesc, string[] orderFields, int recordCount, string sqlWhere, Dictionary<string, object> dbParams, out string sqlText, params string[] selectFields) where T : ModelBase, new();
    }
}
